# -*- coding: utf-8 -*-
#   设计sql语句的功能函数
# @Time    ： 2024/4/9  10:25
# @Auther  ： 徐良
# File     :  sqlFunction.py
import re

from PySide6.QtCore import QDate, Qt
from share.baseFunction import quote
from share.dataFactory import DataFactory
from share.modelFunction import getRecordByFidFromModel
from share.baseFunction import getInstanceType


def escape_string_for_database(s):
    """
    Escape special characters in a string for safe use in a database.
    """
    # 将字符串中的单引号替换为两个单引号
    if getInstanceType(s) == 'str':
        s = s.replace("'", "''")

    # 将字符串中的双引号替换为转义字符加双引号
    # s = s.replace('"', '\\"')
    # # 将字符串中的换行符替换为转义字符加换行符
    # s = s.replace('\n', '\\n')
    # # 将字符串中的制表符替换为转义字符加制表符
    # s = s.replace('\t', '\\t')
    return s


def getSWO(sql):
    """
    分离SQL的 select,where,order段落
    :param self:
    :param sql:
    :return: 字典 ret{s,w,o}  s:select部分，w:where部分，o:order部分
    """
    ss = sql.replace('\n', '').replace(';', '')
    ret = {}
    result = re.search(r'(select.+)(where.+)(group by.+)(order by.+)', ss, re.I)
    result2 = re.search(r'(select.+)(where.+)(group by.+)', ss, re.I)
    result3 = re.search(r'(select.+)(where.+)(order by.+)', ss, re.I)
    result4 = re.search(r'(select.+)(group.+)(order by.+)', ss, re.I)
    result5 = re.search(r'(select.+)(where.+)', ss, re.I)
    result6 = re.search(r'(select.+)(order by.+)', ss, re.I)

    if result is not None:
        ret['s'] = result.group(1)
        ret['w'] = result.group(2)
        ret['g'] = result.group(3)
        ret['o'] = result.group(4)
        return ret

    elif result2 is not None:
        ret['s'] = result2.group(1)
        ret['w'] = result2.group(2)
        ret['g'] = result2.group(3)
        ret['o'] = ''
        return ret

    elif result3 is not None:
        ret['s'] = result3.group(1)
        ret['w'] = result3.group(2)
        ret['o'] = result3.group(3)
        ret['g'] = ''

        return ret

    elif result4 is not None:
        ret['s'] = result4.group(1)
        ret['g'] = result4.group(2)
        ret['o'] = result4.group(3)
        ret['w'] = ''
        return ret

    elif result5 is not None:
        ret['s'] = result5.group(1)
        ret['w'] = result5.group(2)
        ret['o'] = ''
        ret['g'] = ''
        return ret

    elif result6 is not None:
        ret['s'] = result6.group(1)
        ret['o'] = result6.group(2)
        ret['w'] = ''
        ret['g'] = ''
        return ret

    else:
        ret['s'] = ss
        ret['w'] = ''
        ret['o'] = ''
        ret['g'] = ''
        return ret


def getTableNameBySql(ss):
    """
    从Sql语句中得到表名
    :param ss:
    :return:
    """
    pattern = r"(?i)\bFROM\s+(\w+)"

    match = re.search(pattern, ss)

    if match:
        table_name = match.group(1)
        return table_name
    else:
        return None


# 获取insert语句的字段字符串，剔除不参加joinSql
def getInsertFieldStr(fieldList, joinList):
    """
    获取insert语句的字段字符串，剔除不参加joinSql
    :param fieldList:
    :param joinList:
    :return:
    """
    ss = []
    for join, field in zip(joinList, fieldList):
        if join:
            ss.append(field)

    return ','.join(ss)


def getInsertValueStr(r, fList, tList, jList):
    """
    获取记录符合insert语句的字符串
    :param r:
    :param fList:
    :param tList:
    :param jList:
    :return:
    """
    vList = []
    for join, field, ftype in zip(jList, fList, tList):
        if join:
            ret = getFieldValueQuote(field, ftype, r)  # 获取是否带单引号的字段值
            vList.append(ret)
    return ','.join(vList)


# 获取sql Insert语句
def getInsertSql(tableName, fieldsStr, valuesStr):
    """
    获取sql Insert语句
    :param tableName: 表名
    :param fieldsStr: 字段名字符串
    :param valuesStr: 字段内容字符串
    :return:
    """
    sqlT = "insert into {table_name} ({field_str}) values ({value_str});"
    sql = sqlT.format(table_name=tableName, field_str=fieldsStr, value_str=valuesStr)
    return sql


def getDelChildSql(cTableName, cFid):
    """
    删除子表的sql
    :param cTableName: 表名
    :param cFid: fid
    :return:
    """
    sql_T = "DELETE FROM {table_name} WHERE FId={sFid};"
    cSql = sql_T.format(table_name=cTableName, sFid=cFid)
    return cSql


# 获取删除主表记录及关联子表的记录
def getDelParentSqlList(pTableName, pFid, cTableNameList, cFentityIdName='FEntityId'):
    """
    删除主表及其子表关联记录
    :param pTableName: 父表名
    :param pFid: 父表FId
    :param cTableNameList: 子表名列表
    :param cFentityIdName: 子表关联字段名，默认为FEntityId
    :return:
    """
    sqlList = []
    sql_P = "DELETE FROM {table_name} WHERE FId={sFid};"
    pSql = sql_P.format(table_name=pTableName, sFid=pFid)
    sqlList.append(pSql)


    sql_C = "DELETE FROM {table_name} WHERE {fentity_name}={sFid};"

    for ctable in cTableNameList:
        cSql = sql_C.format(table_name=ctable, fentity_name=cFentityIdName, sFid=pFid)
        sqlList.append(cSql)

    return sqlList


# 根据字段类型，得到加单引号或不加单引号的值
def getFieldValueQuote(field, ftype, r):

    # v = r.value(field)
    #
    # type_mapping = {
    #     'integer': lambda x: str(x) if x != "" else "0",
    #     'decimal': lambda x: str(x) if x != "" else "0.0",
    #     'string': lambda x: quote(escape_string_for_database(x)) if x is not None else "",
    #     'time': lambda x: quote(x) if x is not None else "",
    #     'date': lambda x: quote(QDate.fromString(x).toString(Qt.ISODate)) if x is not None else "NULL",
    #     'datetime': lambda x: quote(x) if x is not None else "NULL"
    # }
    # print(field, ftype)
    # return type_mapping.get(ftype.lower(), lambda x: quote(x))(v)

    v = r.value(field)

    # 要判断是否为红蓝字单据，判断是否在
    # if self.__ROBFlag == 1 and self.__ROBFieldName.lower() == field.lower():
    #     v *= -1

    #使用下面的方法，是因为有时model中的增加、插入，默认值是空，与integer,float不符
    if ftype.lower() == 'integer':
        if v == '':
            v = 0
        ep = str(v)
    elif ftype.lower() == 'decimal':
        if v == '':
            v = 0.0
        ep = str(v)
    elif ftype.lower() == 'string':
        ep = escape_string_for_database(v)
        ep = quote(ep)
    elif ftype.lower() == 'time':
        ep = quote(v)
    elif ftype.lower() == 'date':
        if v == '':
            ep = quote(QDate.currentDate().toString(Qt.ISODate))
        else:
            ep = quote(QDate.fromString(v).toString(Qt.ISODate))
    elif ftype.lower() == 'datetime':
        ep = quote(v)
    else:
        ep = quote(v)
    return ep

def getUpdateValueList(r, fList, tList):
    """
    根据记录获取字段值
    :param r:
    :param fList:
    :param tList:
    :param jList:
    :return:
    """
    vList = []
    for field, ftype in zip(fList, tList):
        ret = getFieldValueQuote(field, ftype, r)   # 获取是否带单引号的字段值
        vList.append(ret)

    return vList


def getUpdateExp(fList, vList, jList):
    """
    获取update表达式
    :param fList: 字段名
    :param vList: 字段类型
    :param jList: 是否参与sql
    :return:
    """
    ss = []
    for jsql, field, value in zip(jList, fList, vList):
        if jsql:
            ss.append(f'{field}={value}')

    ret = ','.join(ss)
    return ret


# 根据model 按记录生成update语句
def getUpdateSqlList(tableName, model, fList, tList, jList):
    """
    根据model 按记录生成update语句
    :param tableName:表名
    :param model:
    :param fList:字段列表
    :param tList:字段类型列表
    :param jList: 是否参与语句
    :return: 字符串
    """
    sqlT = "UPDATE {table_name} SET {exp_str} WHERE {field_name} = {vFId};"
    sql_list = []

    for row in range(0, model.rowCount()):
        fid = str(model.record(row).value('FId'))
        rec = model.record(row)
        v_List = getUpdateValueList(rec, fList, tList)
        exp = getUpdateExp(fList, v_List, jList)
        sql = sqlT.format(table_name=tableName, exp_str=exp, field_name='FId', vFId=fid)
        sql_list.append(sql)

    return sql_list


# 通过model, fidList列表获取updata语句
def getUpdateSqlListByModelFIdList(pDict):
    """
    通过model, fidList列表获取updata语句
    :param pDict: fidList,model,fieldList,typeList,joinList
    :return: sql_list
    """
    tableName = pDict['tableName']
    fidList = pDict['fidList']
    model = pDict['model']
    fieldList = pDict['fieldList']
    typeList = pDict['typeList']
    joinList = pDict['joinList']

    sqlT = "UPDATE {table_name} SET {exp_str} WHERE {field_name} = {vFId};"
    sql_list = []

    for fid in fidList:
        if fid != '':
            rec = getRecordByFidFromModel(model, fid)   # 获取该fid的记录
            v_List = getUpdateValueList(rec, fieldList, typeList)

            exp = getUpdateExp(fieldList, v_List, joinList)
            sql = sqlT.format(table_name=tableName, exp_str=exp, field_name='FId', vFId=fid)
            sql_list.append(sql)

    return sql_list


def getInsertSqlList(pDict):
    """
    tableName, model, pid, fieldList, typeList, joinList
    :param pDict:
    :return:
    """
    tableName = pDict['tableName']
    model = pDict['model']
    fieldList = pDict['fieldList']
    typeList = pDict['typeList']
    joinList = pDict['joinList']

    fieldStr = getInsertFieldStr(fieldList, joinList)
    ss = []
    for row in range(0, model.rowCount()):
        if model.record(row).value('FId') == '':
            # fid为空
            rec = model.record(row)
            v_str = getInsertValueStr(rec, fieldList, typeList, joinList)
            sql = getInsertSql(tableName, fieldStr, v_str)
            ss.append(sql)
    return ss


def getInsertAndUpdateSqlList(pDict):
    """
    tableName, model, pid, fieldList, typeList, joinList
    :param pDict:
    :return:
    """

    tableName = pDict['tableName']
    model = pDict['model']
    fieldList = pDict['fieldList']
    typeList = pDict['typeList']
    joinList = pDict['joinList']

    fidList = list(model.listUpdate)

    sqlUpdate_T = "UPDATE {table_name} SET {exp_str} WHERE {field_name} = {vFId};"

    sql_list = []

    # 1 先组织update语句
    for fid in fidList:
        if fid != '':
            rec = getRecordByFidFromModel(model, fid)   # 获取该fid的记录
            v_List = getUpdateValueList(rec, fieldList, typeList)
            exp = getUpdateExp(fieldList, v_List, joinList)
            sql = sqlUpdate_T.format(table_name=tableName, exp_str=exp, field_name='FId', vFId=fid)
            sql_list.append(sql)

    # 2 组织Insert语句
    fieldStr = getInsertFieldStr(fieldList, joinList)
    ss = []
    for row in range(0, model.rowCount()):
        if model.record(row).value('FId') == '':
            # fid为空
            rec = model.record(row)
            v_str = getInsertValueStr(rec, fieldList, typeList, joinList)
            sql = getInsertSql(tableName, fieldStr, v_str)
            ss.append(sql)

    sql_list.extend(ss)

    return sql_list


def getDelSqlByIdList(tableName, idList):
    """
    获取delete 语句
    :param tableName:
    :param idList:
    :return:
    """
    sql = "DELETE FROM {table_name} WHERE fid in (idstr);".format(table_name=tableName, idstr=','.join(idList))
    return sql


# ********************************  自动取父id（PID）的insert和update语句 **************
def getInsertSqlListFindPID(pDict):
    """
    获取多条insert语句，每条需要根据fnumber查找FID
    :param pDict:
    'tableName': 表名
    'fidList': fid, 用于update
    'fieldList': 字段名列表,
    'typeList': 字段类型列表,
    'joinList': 是否加入sql语句
    :return:
    """
    tableName = pDict['tableName']
    model = pDict['model']
    fieldList = pDict['fieldList']
    typeList = pDict['typeList']
    joinList = pDict['joinList']

    fieldStr = getInsertFieldStr(fieldList, joinList)
    ss = []
    for row in range(0, model.rowCount()):
        if model.record(row).value('FId') == '':
            # fid为空
            rec = model.record(row)
            v_str = getInsertValueStrFindPID(rec, fieldList, typeList, joinList, tableName)
            sql = getInsertSql(tableName, fieldStr, v_str)
            ss.append(sql)
    return ss


def getInsertValueStrFindPID(r, fList, tList, jList, tableName):
    """
    根据Fnumber,得到字段FparentId值,获取记录符合insert语句的字符串
    :param tableName:
    :param r:
    :param fList:
    :param tList:
    :param jList:
    :return:
    """
    vList = []
    for join, field, ftype in zip(jList, fList, tList):
        if join:
            ret = getFieldValueQuoteFindPID(field, ftype, r, tableName)  # 获取是否带单引号的字段值
            vList.append(ret)
    return ','.join(vList)


# 根据字段类型，得到加单引号或不加单引号的值
def getFieldValueQuoteFindPID(field, ftype, r, tableName):
    """
    根据Fnumber,得到字段FparentId值, 根据字段类型，得到加单引号或不加单引号的值
    :param tableName:
    :param field:
    :param ftype:
    :param r:
    :return:
    """
    if field.lower() == 'fparentid':
        num = r.value('FNumber')
        v = getPIdByFNumber(tableName, num)
    else:
        v = r.value(field)

    # 使用下面的方法，是因为有时model中的增加、插入，默认值是空，与integer,float不符
    if ftype.lower() == 'integer':
        if v == '':
            v = 0
        ep = str(v)
    elif ftype.lower() == 'decimal':
        if v == '':
            v = 0.0
        ep = str(v)
    elif ftype.lower() == 'string':
        ep = escape_string_for_database(v)
        ep = quote(ep)
    elif ftype.lower() == 'time':
        ep = quote(v)
    elif ftype.lower() == 'date':
        if v == '':
            ep = quote(QDate.currentDate().toString(Qt.ISODate))
        else:
            ep = quote(v)
    elif ftype.lower() == 'datetime':
        ep = quote(v)
    else:
        ep = quote(v)
    return ep


def getPIdByFNumber(tableName, FNumber):
    """
    获取PID
    :param tableName: 表名
    :param FNumber: 编码,.分
    :return:
    """
    sqlT = "select FId from {tname} where left(FNumber,{iLen})='{sNum}'"
    slist = FNumber.split('.')
    ilen = len(slist)
    ss = slist[:ilen - 1]
    upStr = ','.join(ss)
    upStrLen = len(upStr)

    sql = sqlT.format(tname=tableName, iLen=upStrLen, sNum=upStr)
    model = DataFactory.getQueryModelForSql(sql)
    if model.rowCount() == 0:
        return 0
    else:
        return model.record(0).value('FId')


# ********************************  增加where条件   **********************************
def addWhereFentityid(sSql, sid):
    """
    增加子表的父id条件
    :param sSql:
    :param sid:
    :return:
    """
    sExp = "FEntityId={iFid}".format(iFid=str(sid))
    swd = getSWO(sSql)
    if swd['w'] == '':
        w = 'where ' + sExp
    else:
        w = swd['w'] + ' and ' + sExp

    if swd['g'] != '':
        ss = swd['s'] + ' ' + w + ' ' + swd['g'] + ' ' + swd['o']
    else:
        ss = swd['s'] + ' ' + w + ' ' + swd['o']
    return ss


def addWhereCondition_Str(sSql, condiStr):
    """
    增加一个条件，
    :param sSql:
    :param condiStr:字符串表达式
    :return:
    """
    swd = getSWO(sSql)
    if swd['w'] == '':
        w = 'where ' + condiStr
    else:
        w = swd['w'] + ' and ' + condiStr

    if swd['g'] != '':
        ss = swd['s'] + ' ' + w + ' ' + swd['g'] + ' ' + swd['o']
    else:
        ss = swd['s'] + ' ' + w + ' ' + swd['o']
    return ss




